package com.xing.lei.happy.thrid.flh.sql.service;

import com.xing.lei.happy.thrid.flh.sql.enums.SqlType;
import com.xing.lei.happy.thrid.flh.sql.model.CreateTableParts;
import com.xing.lei.happy.thrid.flh.sql.model.InsertStatement;
import com.xing.lei.happy.thrid.flh.sql.utils.SqlTypeRecognizer;
import com.xing.lei.happy.thrid.flh.sql.utils.SqlUtils;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Mysql数据库SQL转换器
 * 负责将Oracle数据库的SQL语句转换为Mysql数据库可重复执行sql格式
 */
public class MySqlSqlConverter implements SqlTypeConverter {
    @Override
    public String convert(List<String> sqlList) {
        Map<SqlType, List<String>> typeMap = new LinkedHashMap<>();
        for (String sql : sqlList) {
            for (String part : sql.split(";")) {
                String trimmed = part.trim();
                if (!trimmed.isEmpty()) {
                    SqlType type = SqlTypeRecognizer.recognize(trimmed);
                    typeMap.computeIfAbsent(type, k -> new ArrayList<>()).add(trimmed);
                }
            }
        }

        StringBuilder sb = new StringBuilder();
        sb.append("\n-- Mysql可重复执行脚本\n");
        // 处理建表语句
        List<String> createTableList = typeMap.getOrDefault(SqlType.CREATE_TABLE, Collections.emptyList());
        if (!createTableList.isEmpty()) {
            sb.append("-- 创建表\n");
            for (String sql : createTableList) {
                CreateTableParts parts = parseCreateTableParts(sql);
                sb.append(parts.getCreateTableSql().replaceFirst("CREATE TABLE", "CREATE TABLE IF NOT EXISTS")).append(";\n");
                if (parts.getPrimaryKeySql() != null) {
                    sb.append(parts.getPrimaryKeySql()).append(";\n");
                }
                for (String idx : parts.getIndexSqls()) {
                    sb.append(idx).append(";\n");
                }
            }
            sb.append("\n");
        }

        // 处理新增列
        List<String> addColList = typeMap.getOrDefault(SqlType.ADD_COLUMN, Collections.emptyList());
        if (!addColList.isEmpty()) {
            sb.append("-- 新增列\n");
            for (String sql : addColList) {
                String converted = convertAlterColumnType(sql, "MySQL");
                sb.append(converted).append(";\n");
            }
            sb.append("\n");
        }

        // 处理修改列
        List<String> modifyColList = typeMap.getOrDefault(SqlType.MODIFY_COLUMN, Collections.emptyList());
        if (!modifyColList.isEmpty()) {
            sb.append("-- 修改列\n");
            for (String sql : modifyColList) {
                String converted = convertAlterColumnType(sql, "MySQL");
                sb.append(converted);
                if (!converted.trim().endsWith(";")) sb.append(";");
                sb.append("\n");
            }
            sb.append("\n");
        }

        // 处理新增行
        List<String> insertList = typeMap.getOrDefault(SqlType.INSERT, Collections.emptyList());
        if (!insertList.isEmpty()) {
            Map<String, List<InsertStatement>> tableGroups = new LinkedHashMap<>();
            for (String sql : insertList) {
                InsertStatement stmt = parseSingleInsert(sql);
                tableGroups.computeIfAbsent(stmt.getTable(), k -> new ArrayList<>()).add(stmt);
            }
            for (Map.Entry<String, List<InsertStatement>> entry : tableGroups.entrySet()) {
                String table = entry.getKey();
                List<InsertStatement> stmts = entry.getValue();
                sb.append("--新增行 ").append(table).append("\n");
                String columns = stmts.get(0).getColumns();
                List<String> ids = new ArrayList<>();
                for (InsertStatement s : stmts) ids.add("'" + s.getId() + "'");
                sb.append("INSERT INTO ").append(table).append(" (").append(columns).append(")\nSELECT * FROM (\n");
                for (int i = 0; i < stmts.size(); i++) {
                    InsertStatement s = stmts.get(i);
                    if (i > 0) sb.append("    UNION ALL\n");
                    sb.append("    SELECT ");
                    String[] colArr = s.getColumns().split(",");
                    String[] valArr = SqlUtils.splitValues(s.getValues().replaceAll("SYSDATE","NOW()").replaceAll("sysdate","NOW()"));
                    if (colArr.length != valArr.length) {
                        System.err.println("[警告] MySQL批量insert字段数和值数不一致，已跳过: " + s.getTable() + " (" + columns + ") VALUES (" + s.getValues() + ")");
                        continue;
                    }
                    for (int j = 0; j < valArr.length; j++) {
                        String value = valArr[j].trim();
                        if (i == 0) {
                            sb.append(value).append(" AS ").append(colArr[j].trim());
                        } else {
                            sb.append(value);
                        }
                        if (j < valArr.length - 1) sb.append(", ");
                    }
                    sb.append("\n");
                }
                sb.append(") AS tmp\nWHERE NOT EXISTS (SELECT 1 FROM ").append(table).append(" WHERE " + stmts.get(0).getIdName() + " IN (").append(String.join(",", ids)).append(")\n) LIMIT 1;\n");
                sb.append("\n");
            }
        }
        return sb.toString();
    }

    private CreateTableParts parseCreateTableParts(String sql) {
        CreateTableParts parts = new CreateTableParts();
        Pattern createTablePattern = Pattern.compile("(?i)CREATE\\s+TABLE\\s+([a-zA-Z0-9_]+)");
        Matcher matcher = createTablePattern.matcher(sql);
        if (!matcher.find()) {
            throw new IllegalArgumentException("无法解析建表SQL: " + sql);
        }
        String tableName = matcher.group(1);
        parts.setTableName(tableName);

        int idxLPar = sql.indexOf('(');
        int idxRPar = sql.lastIndexOf(')');
        if (idxLPar < 0 || idxRPar < 0) throw new IllegalArgumentException("无法解析建表SQL: " + sql);

        String fieldsRaw = sql.substring(idxLPar + 1, idxRPar);
        String[] fieldLines = fieldsRaw.split("\\n");
        StringBuilder prettyFields = new StringBuilder();
        for (String line : fieldLines) {
            String trim = line.trim();
            if (!trim.isEmpty()) {
                String converted = SqlUtils.convertFieldType(trim, "MySQL");
                prettyFields.append("    ").append(converted).append("\n");
            }
        }
        String prettyFieldsStr = prettyFields.toString().replaceAll("\n$", "");
        
        String remain = sql.substring(idxRPar + 1).trim();
        Pattern pkPat = Pattern.compile("PRIMARY\\s+KEY\\s+([a-zA-Z0-9_]+)", Pattern.CASE_INSENSITIVE);
        Matcher pkMat = pkPat.matcher(remain);
        if (pkMat.find()) {
            String pkCol = pkMat.group(1);
            parts.setCreateTableSql("CREATE TABLE " + tableName + "\n(\n" + prettyFieldsStr + ",\n    PRIMARY KEY (" + pkCol + ")\n)");
        } else {
            parts.setCreateTableSql("CREATE TABLE " + tableName + "\n(\n" + prettyFieldsStr + "\n)");
        }

        Pattern idxPat = Pattern.compile("INDEX\\s+([a-zA-Z0-9_,]+)", Pattern.CASE_INSENSITIVE);
        Matcher idxMat = idxPat.matcher(remain);
        while (idxMat.find()) {
            String idxCols = idxMat.group(1);
            String[] columns = idxCols.split(",");
            for (String col : columns) {
                String colName = col.trim();
                parts.getIndexSqls().add("CREATE INDEX IDX_" + tableName.toUpperCase() + "_" + colName.toUpperCase() + " ON " + tableName + " (" + colName + ")");
            }
        }
        return parts;
    }

    private InsertStatement parseSingleInsert(String sql) {
        Pattern pattern = Pattern.compile(
                "(?i)insert\\s+into\\s+([^\\(\\s]+)\\s*\\(([^)]+)\\)\\s*values\\s*\\((.*)\\)",
                Pattern.DOTALL
        );
        Matcher matcher = pattern.matcher(sql.trim());
        if (matcher.find()) {
            String tableName = matcher.group(1).trim();
            String columns = matcher.group(2).trim();
            String values = matcher.group(3).trim();
            return new InsertStatement(tableName, columns, values);
        }
        throw new IllegalArgumentException("无法解析insert语句: " + sql);
    }

    private String convertAlterColumnType(String sql, String dbType) {
        Pattern addPat = Pattern.compile("ALTER\\s+TABLE\\s+([a-zA-Z0-9_]+)\\s+ADD\\s+(.+)", Pattern.CASE_INSENSITIVE);
        Pattern modPat = Pattern.compile("ALTER\\s+TABLE\\s+([a-zA-Z0-9_]+)\\s+MODIFY\\s+(.+)", Pattern.CASE_INSENSITIVE);
        Matcher m = addPat.matcher(sql);
        if (m.find()) {
            String table = m.group(1);
            String colDef = m.group(2).trim();
            String converted = SqlUtils.convertFieldType(colDef, dbType);
            return "ALTER TABLE " + table + " ADD " + converted;
        }
        m = modPat.matcher(sql);
        if (m.find()) {
            String table = m.group(1);
            String colDef = m.group(2).trim();
            String converted = SqlUtils.convertFieldType(colDef, dbType);
            return "ALTER TABLE " + table + " MODIFY " + converted;
        }
        return sql;
    }
} 